using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Office.Interop;
using System.Data.Entity;
using MigraDoc.DocumentObjectModel;
using MigraDoc.Rendering;
using <Application>.<Business>;

namespace <Application>.<Data>
{
	public class <Database>DBContext : DbContext
	{
		public <Database>DBContext()
			: base("<Database>DBContext")
		{
			Database.SetInitializer<<Database>DBContext>(null);
			Configuration.ProxyCreationEnabled = false;
		}

	<RTable Loop>
			public DbSet<<OwnerU>_<TableU>> <OwnerU>_<TableU> { get; set; }

	</RTable Loop>
	}

	public class <Database>DataClass
	{
		public static void ExportToExcel(string Type, string Head, System.Windows.Forms.DataGridView Grid)
		{
			try
			{
				if (Grid.ColumnCount > 0)
				{
					Microsoft.Office.Interop.Excel.Application oExcel;
					Microsoft.Office.Interop.Excel.Workbook oBook;
					Microsoft.Office.Interop.Excel.Worksheet oSheet;

					oExcel = new Microsoft.Office.Interop.Excel.Application();
					oExcel.Visible = false;

					oBook = (Microsoft.Office.Interop.Excel.Workbook)(oExcel.Workbooks.Add(System.Type.Missing));
					oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oBook.ActiveSheet;

					oSheet.Range["B2"].Value = Head;
					oSheet.Range["B2"].Font.Bold = true;

					if (Type == "One")
					{
						for (int s = 0; s <= Grid.ColumnCount - 1; s++)
						{
							oSheet.Range["B" + (4 + s)].Value = Grid.Columns[s].HeaderText;
							oSheet.Range["C" + (4 + s)].Value = Grid.SelectedRows[0].Cells[s].Value;
						}

						oSheet.Columns["B:B"].ColumnWidth = 20;
						oSheet.Columns.AutoFit();

					}
					else if (Type == "Many")
					{
						object[,] DataArrayHead = new object[1, Grid.ColumnCount];
						for (int s = 0; s <= Grid.ColumnCount - 1; s++)
						{
							DataArrayHead[0, s] = Grid.Columns[s].HeaderText;
						}
	
						oSheet.Range["B4"].Resize[1, Grid.ColumnCount].Value = DataArrayHead;
						oSheet.Range["B4"].Resize[1, Grid.ColumnCount].Font.Bold = true;
	
						object[,] DataArray = new object[Grid.RowCount, Grid.ColumnCount];
						for (int r = 0; r <= Grid.RowCount - 1; r++)
						{
							for (int s = 0; s <= Grid.ColumnCount - 1; s++)
							{
								DataArray[r, s] = Grid[s, r].Value;
							}
						}
	
						oSheet.Range["B5"].Resize[Grid.RowCount, Grid.ColumnCount].Value = DataArray;
						oSheet.Columns.AutoFit();
	
					}
	
					oExcel.Visible = true;
					oExcel = null;
					oSheet = null;
				}
			}
			catch
			{
			}
		}

        public static void ExportToWord(string Type, string Head, System.Windows.Forms.DataGridView Grid)
        {
            try
            {
                if (Grid.ColumnCount > 0)
                {
                    Microsoft.Office.Interop.Word.Application oWord;
                    Microsoft.Office.Interop.Word.Document oDoc;
                    Microsoft.Office.Interop.Word.Table oTable;
                    Microsoft.Office.Interop.Word.Paragraph oPara1;

                    oWord = new Microsoft.Office.Interop.Word.Application();
                    oWord.Visible = false;
                    oDoc = (Microsoft.Office.Interop.Word.Document)(oWord.Documents.Add(System.Type.Missing));
                    var _with1 = oDoc.PageSetup;
                    _with1.PaperSize = Microsoft.Office.Interop.Word.WdPaperSize.wdPaperA4;
                    _with1.Orientation = Microsoft.Office.Interop.Word.WdOrientation.wdOrientLandscape;
                    _with1.LeftMargin = oWord.CentimetersToPoints(1);
                    _with1.BottomMargin = oWord.CentimetersToPoints(1);
                    _with1.TopMargin = oWord.CentimetersToPoints(1);

                    oPara1 = (Microsoft.Office.Interop.Word.Paragraph)(oDoc.Content.Paragraphs.Add(System.Type.Missing));
                    oPara1.Range.Text = Head;
                    oPara1.Range.Font.Bold = 0;
                    oPara1.Range.Font.Size = 8;
                    oPara1.Format.SpaceAfter = 8;
                    oPara1.Range.InsertParagraphAfter();


                    if (Type == "One")
                    {
                        oTable = oDoc.Tables.Add(oDoc.Bookmarks.get_Item("\\endofdoc").Range, Grid.ColumnCount, 2);
                        oTable.Range.ParagraphFormat.SpaceAfter = 2;

                        for (int i = 0; i <= Grid.ColumnCount - 1; i++)
                        {
                            oTable.Cell(i + 1, 1).Range.InsertAfter(Grid.Columns[i].HeaderText);
                            oTable.Cell(i + 1, 2).Range.Text = Convert.ToString(Grid.SelectedRows[0].Cells[i].Value);
                        }

                        oTable.Borders.InsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleSingle;
                        oTable.Borders.OutsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleDouble;

                        oTable.AllowAutoFit = true;
                        oTable.Columns.AutoFit();

                    }
                    else if (Type == "Many")
                    {
                        oTable = oDoc.Tables.Add(oDoc.Bookmarks.get_Item("\\endofdoc").Range, Grid.RowCount + 1, Grid.ColumnCount);
                        oTable.Range.ParagraphFormat.SpaceAfter = 2;

                        for (int i = 0; i <= Grid.ColumnCount - 1; i++)
                        {
                            oTable.Cell(1, i + 1).Range.InsertAfter(Grid.Columns[i].HeaderText);
                        }

                        for (int r = 0; r <= Grid.RowCount - 1; r++)
                        {
                            for (int s = 0; s <= Grid.ColumnCount - 1; s++)
                            {
                                oTable.Cell(r + 2, s + 1).Range.Text = Convert.ToString(Grid[s, r].Value);
                            }
                        }

                        oTable.Rows[1].Cells.Shading.BackgroundPatternColorIndex = Microsoft.Office.Interop.Word.WdColorIndex.wdGray25;

                        oTable.Borders.InsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleSingle;
                        oTable.Borders.OutsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleDouble;

                        oTable.AllowAutoFit = true;
                        oTable.Columns.AutoFit();

                    }

                    oWord.Visible = true;
                    oWord.NormalTemplate.Saved = true;
                    oWord = null;
                    oDoc = null;
                }
            }
            catch
            {
            }
        }

		//public static void ExportToPDF(string Type, string Head, System.Windows.Forms.DataGridView Grid)
		//{
		//    try
		//    {
		//        if (Grid.ColumnCount > 0)
		//        {
		//            Microsoft.Office.Interop.Word.Application oWord;
		//            Microsoft.Office.Interop.Word.Document oDoc;
		//            Microsoft.Office.Interop.Word.Table oTable;
		//            Microsoft.Office.Interop.Word.Paragraph oPara1;
	
		//            oWord = new Microsoft.Office.Interop.Word.Application();
		//            oWord.Visible = false;
		//            oDoc = (Microsoft.Office.Interop.Word.Document)(oWord.Documents.Add(System.Type.Missing));
		//            var _with1 = oDoc.PageSetup;
		//            _with1.PaperSize = Microsoft.Office.Interop.Word.WdPaperSize.wdPaperA4;
		//            _with1.Orientation = Microsoft.Office.Interop.Word.WdOrientation.wdOrientLandscape;
		//            _with1.LeftMargin = oWord.CentimetersToPoints(1);
		//            _with1.BottomMargin = oWord.CentimetersToPoints(1);
		//            _with1.TopMargin = oWord.CentimetersToPoints(1);

		//            oPara1 = (Microsoft.Office.Interop.Word.Paragraph)(oDoc.Content.Paragraphs.Add(System.Type.Missing));
		//            oPara1.Range.Text = Head;
		//            oPara1.Range.Font.Bold = 0;
		//            oPara1.Range.Font.Size = 8;
		//            oPara1.Format.SpaceAfter = 8;
		//            oPara1.Range.InsertParagraphAfter();


		//            if (Type == "One")
		//            {
		//                oTable = oDoc.Tables.Add(oDoc.Bookmarks.get_Item("\\endofdoc").Range, Grid.ColumnCount, 2);
		//                oTable.Range.ParagraphFormat.SpaceAfter = 2;
	
		//                for (int i = 0; i <= Grid.ColumnCount - 1; i++)
		//                {
		//                    oTable.Cell(i + 1, 1).Range.InsertAfter(Grid.Columns[i].HeaderText);
		//                    oTable.Cell(i + 1, 2).Range.Text = Convert.ToString(Grid.SelectedRows[0].Cells[i].Value);
		//                }
	
		//                oTable.Borders.InsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleSingle;
		//                oTable.Borders.OutsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleDouble;
	
		//                oTable.AllowAutoFit = true;
		//                oTable.Columns.AutoFit();
	
		//            }
		//            else if (Type == "Many")
		//            {
		//                oTable = oDoc.Tables.Add(oDoc.Bookmarks.get_Item("\\endofdoc").Range, Grid.RowCount + 1, Grid.ColumnCount);
		//                oTable.Range.ParagraphFormat.SpaceAfter = 2;
	
		//                for (int i = 0; i <= Grid.ColumnCount - 1; i++)
		//                {
		//                    oTable.Cell(1, i + 1).Range.InsertAfter(Grid.Columns[i].HeaderText);
		//                }

		//                for (int r = 0; r <= Grid.RowCount - 1; r++)
		//                {
		//                    for (int s = 0; s <= Grid.ColumnCount - 1; s++)
		//                    {
		//                        oTable.Cell(r + 2, s + 1).Range.Text = Convert.ToString(Grid[s, r].Value);
		//                    }
		//                }

		//                oTable.Rows[1].Cells.Shading.BackgroundPatternColorIndex = Microsoft.Office.Interop.Word.WdColorIndex.wdGray25;

		//                oTable.Borders.InsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleSingle;
		//                oTable.Borders.OutsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleDouble;
	
		//                oTable.AllowAutoFit = true;
		//                oTable.Columns.AutoFit();
	
		//            }

		//            string FilePath = System.IO.Path.GetTempPath() + "\\" + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + ".pdf";
		//            oDoc.ExportAsFixedFormat(FilePath, Microsoft.Office.Interop.Word.WdExportFormat.wdExportFormatPDF, true);
		//            ((Microsoft.Office.Interop.Word._Document)(oDoc)).Close(Microsoft.Office.Interop.Word.WdSaveOptions.wdDoNotSaveChanges);
		//            ((Microsoft.Office.Interop.Word._Application)(oWord)).Quit(); 
		//        }
		//    }
		//    catch
		//    {
		//    }
		//}

		public static void ExportToPDF(string Type, string Head, System.Windows.Forms.DataGridView Grid, System.Windows.Forms.DataGridViewRow GridRow)
		{
			try
			{
				DataTable dt = default(DataTable);
				if (Type == "One")
				{
					dt = DataGridViewRow_To_DataTable(Grid, GridRow, Head);
				}
				else
				{
					dt = DataGridView_To_DataTable(Grid, Head);
				}
				PDFform pdfForm = new PDFform(dt, Head, Type);

				Document document = pdfForm.CreateDocument();
				document.UseCmykColor = true;
				PdfDocumentRenderer pdfRenderer = new PdfDocumentRenderer(true);
				pdfRenderer.Document = document;
				pdfRenderer.RenderDocument();

				string FilePath = System.IO.Path.GetTempPath() + "\\" + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + ".pdf";
				pdfRenderer.Save(FilePath);
				System.Diagnostics.Process.Start(FilePath);
			}
			catch (Exception ex)
			{
				Console.WriteLine(ex.Message);
				Console.ReadLine();
			}
		}

		//Converts the DataGridView to DataTable
		public static DataTable DataGridView_To_DataTable(System.Windows.Forms.DataGridView dgv, String tblName, int minRow = 0)
		{
			DataTable dt = new DataTable(tblName);

			// Header columns
			foreach (System.Windows.Forms.DataGridViewColumn column in dgv.Columns)
			{
				DataColumn dc = new DataColumn(column.Name.ToString());
				dt.Columns.Add(dc);
			}

			// Data cells
			for (int i = 0; i <= dgv.Rows.Count - 1; i++)
			{
				System.Windows.Forms.DataGridViewRow row = dgv.Rows[i];
				DataRow dr = dt.NewRow();
				for (int j = 0; j <= dgv.Columns.Count - 1; j++)
				{
					dr[j] = (row.Cells[j].Value == null) ? "" : row.Cells[j].Value.ToString();
				}
				dt.Rows.Add(dr);
			}

			// Related to the bug arround min size when using ExcelLibrary for export
			for (int i = dgv.Rows.Count; i <= minRow - 1; i++)
			{
				DataRow dr = dt.NewRow();
				for (int j = 0; j <= dt.Columns.Count - 1; j++)
				{
					dr[j] = "  ";
				}
				dt.Rows.Add(dr);
			}
			return dt;
		}

		//Converts the DataGridViewRow to DataTable
		public static DataTable DataGridViewRow_To_DataTable(System.Windows.Forms.DataGridView dgv, System.Windows.Forms.DataGridViewRow dgvr, String tblName, int minRow = 0)
		{
			DataTable dt = new DataTable(tblName);

			// Header columns
			DataColumn dc1 = new DataColumn("Column");
			dt.Columns.Add(dc1);
			DataColumn dc2 = new DataColumn("Value");
			dt.Columns.Add(dc2);

			foreach (System.Windows.Forms.DataGridViewColumn column in dgv.Columns)
			{
				dt.Rows.Add(dgv.Columns[column.Index].Name.ToString(), dgvr.Cells[column.Index].Value);
			}
			return dt;
		}
	}
}
